﻿using System;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Activation;
using System.Collections.Generic;
using System.Text;

using System.Web.Configuration;
using System.Data.SqlClient;

namespace MyPCTagging.Web
{
    [ServiceContract(Namespace = "")]
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    public class DatabaseService
    {
        private static SqlConnection myConnection = null;
        private static SqlDataReader myReader = null;

        /* Read from file "Web.config" */
        private static String cntString = WebConfigurationManager.AppSettings["DatabaseServiceConnectString"];
        private static String ruleName = WebConfigurationManager.AppSettings["CurrentRuleName"];

        [OperationContract]
        /*
         * Get all device information, include name and id
         */
        public DeviceNode GetAllDevicesInfo()
        {          
            InitConnection();

            DeviceNode root = new DeviceNode();
            root.id = 1;
            root.name = GetGroupNameByID(1);
            root.type = DeviceNodeType.Group;
            root.children = new List<DeviceNode>();
            AddChildrenNode(ref root);

            CloseConnection();

            return root;
        }

        /*
         * Add children to group node, not affect device node
         */
        private static void AddChildrenNode(ref DeviceNode node)
        {
            if (node.type != DeviceNodeType.Group)
            {
                return;
            }
            List<int> idList;
            List<String> nameList;
            // add sub-groups
            idList = GetSubGroupsID(node.id);
            nameList = GetSubGroupsName(node.id);
            for (int i = 0; i < idList.Count; i++ )
            {
                DeviceNode gNode = new DeviceNode();
                gNode.id = idList[i];
                gNode.name = nameList[i];
                gNode.type = DeviceNodeType.Group;
                gNode.children = new List<DeviceNode>();
                AddChildrenNode(ref gNode);
                node.children.Add(gNode);
            }

            // add members
            idList = GetMembersID(node.id);
            nameList = GetMembersName(node.id);
            for (int i = 0; i < idList.Count; i++ )
            {
                DeviceNode cNode = new DeviceNode();
                cNode.id = idList[i];
                cNode.name = nameList[i];
                cNode.type = DeviceNodeType.Device;
                node.children.Add(cNode);
            }
        }

        [OperationContract]
        /*
         * Get all records of some devices
         */
        public List<String> GetAllRecords(List<int> idList)
        {
            if (idList == null) return null;

            InitConnection();

            List<String> recordList = new List<String>();
            for (int i = 0; i < idList.Count; i++ )
            {
                recordList.Add( GetConcernedValue(idList[i], ruleName) );
            }

            CloseConnection();

            return recordList;
        }

        [OperationContract]
        /*
         * Get latest record of some devices
         */
        public List<String> GetLatestRecords(List<int> idList)
        {
            if (idList == null) return null;

            InitConnection();

            String wholeRecord, latestRecord;
            String[] recordArray;
            List<String> recordList = new List<String>();
            for (int i = 0; i < idList.Count; i++)
            {
                wholeRecord = GetConcernedValue(idList[i], ruleName);
                if (wholeRecord == null)
                {
                    // no record for this device
                    latestRecord = null;
                }
                else
                {
                    /*
                    // for test, get oldest one
                    latestRecord = wholeRecord.Substring( 
                        0, wholeRecord.IndexOf('&')+1 );
                    */

                    // split record to get latest one
                    recordArray = wholeRecord.Split('&');
                    if (recordArray.Length > 0)
                    {
                        latestRecord = recordArray[recordArray.Length - 1];
                    }
                    else
                    {
                        latestRecord = null;
                    }
                }
                recordList.Add(latestRecord);
            }

            CloseConnection();

            return recordList;
        }

        /*
         * Ready for connection
         */
        public static void InitConnection()
        {
            if (myConnection == null)
            {
                myConnection = new SqlConnection(cntString);
                myConnection.Open();
            }
        }
        
        /*
         * Close the connection
         */
        public static void CloseConnection()
        {
            if (myConnection != null)
            {
                myConnection.Close();
                myConnection = null;
            }
        }

        /*
         * Execute command and return the SqlDataReader 
         */
        public static SqlDataReader ExecuteReader(String cmdString)
        {
            try
            {
                SqlCommand myCmd = new SqlCommand(cmdString, myConnection);
                if (myReader != null && !myReader.IsClosed)
                {
                    myReader.Close();
                }
                myReader = myCmd.ExecuteReader();
                return myReader;
            }
            catch (Exception e)
            {
                return null;
            }
        }

        /*
         * Get group name by id, use table "dbo.Collection_G"
         * (If not found, return null)
         */
        public static String GetGroupNameByID(int groupID)
        {
            String cmdString = @"SELECT CollectionName FROM dbo.Collections_G
                             WHERE CollectionID=" + groupID;
            SqlDataReader reader = ExecuteReader(cmdString);
            if (reader != null && reader.Read())
            {
                return reader["CollectionName"].ToString();
            }
            return null;
        }

        /*
         * Get member name by id, use table "dbo.CollectionMembers"
         * (If not found, return null)
         */
        public static String GetMemberNameByID(int machineID)
        {
            String cmdString = @"SELECT Name FROM dbo.CollectionMembers
                             WHERE MachineID=" + machineID;
            SqlDataReader reader = ExecuteReader(cmdString);
            if (reader != null && reader.Read())
            {
                return reader["Name"].ToString();
            }
            return null;
        }

        /*
         * Get sub-groups id, use table "dbo.Collection_SubCollections"
         */
        public static List<int> GetSubGroupsID(int groupID)
        {
            String cmdString = @"SELECT SubCollectionID FROM dbo.Collection_SubCollections
                             WHERE CollectionID=" + groupID;
            SqlDataReader reader = ExecuteReader(cmdString);
            List<int> idList = new List<int>();
            if (reader != null)
            {
                while (reader.Read())
                {
                    idList.Add(Convert.ToInt32(reader["SubCollectionID"]));
                }
            }
            return idList;
        }

        /*
         * Get sub-groups name, use function "GetSubGroupsID" & "GetGroupNameByID"
         */
        public static List<String> GetSubGroupsName(int groupID)
        {
            List<int> idList = GetSubGroupsID(groupID);
            List<String> nameList = new List<String>();
            for (int i = 0; i < idList.Count; i++)
            {
                nameList.Add(GetGroupNameByID(idList[i]));
            }
            return nameList;
        }

        /*
         * Get members id, use table "dbo.Collecions_G"	& "dbo.CollectionMembers"
         */
        public static List<int> GetMembersID(int groupID)
        {
            String cmdString = @"SELECT cm.MachineID FROM dbo.CollectionMembers AS cm
                             INNER JOIN dbo.Collections_G AS cg ON cm.SiteID=cg.SiteID 
                             WHERE cg.CollectionID=" + groupID;
            SqlDataReader reader = ExecuteReader(cmdString);
            List<int> idList = new List<int>();
            if (reader != null)
            {
                while (reader.Read())
                {
                    idList.Add(Convert.ToInt32(reader["MachineID"]));
                }
            }
            return idList;
        }

        /*
         * Get members name, use table "dbo.Collecions_G" & "dbo.CollectionMembers"	
         */
        public static List<String> GetMembersName(int groupID)
        {
            String cmdString = @"SELECT cm.Name FROM dbo.CollectionMembers AS cm
                             INNER JOIN dbo.Collections_G AS cg ON cm.SiteID=cg.SiteID 
                             WHERE cg.CollectionID=" + groupID;
            SqlDataReader reader = ExecuteReader(cmdString);
            List<String> nameList = new List<String>();
            if (reader != null)
            {
                while (reader.Read())
                {
                    nameList.Add(reader["Name"].ToString());
                }
            }
            return nameList;
        }

        /*
         * Get concerned value, use table "dbo.CI_CurrentRuleDetail"
         */
        public String GetConcernedValue(int machineID, String ruleName)
        {
            String cmdString = @"SELECT PreReifiedValue FROM dbo.CI_CurrentRuleDetail
                             WHERE ItemKey=" + machineID + "AND ConstraintName='" + ruleName + "'";
            SqlDataReader reader = ExecuteReader(cmdString);
            if (reader != null && reader.Read())
            {
                return reader["PreReifiedValue"].ToString();
            }
            return null;
        }
    }
}
